WITH句で変換後に目的のテーブルへINSERT SELECTしてみた

#SQL

#Amazon Redshift

クルトン

2023.12.20

みなさんこんにちは、クルトンです！

INSERTする時に、テーブルの値を変換する必要があったのですが、タイトルの内容でズバリそのものな情報が見つからなかったので備忘録として今回は書いてみます。

やること

まず3つのテーブルを作成します。テーブルAとテーブルBにデータが入っており、それらのデータをWITH句を使って処理した後に、テーブルCにINSERTする処理を走らせる、という事をします。

動作検証環境

実行環境がすでにあったため、DBeaver上でRedshiftに繋いでSQLを実行しています。お好みの環境でも動くかと思います。

テーブル作成

今回は動作検証のため、変換元のテーブル2つは簡単なものを作成します。

まずは一つ目です。blog_table_aという名前で作成します。

create table <スキーマ名>.blog_table_a(
    ID integer identity(1,1) -- 自動採番で1始まりでid割り振り
    , some_data integer
    , table_name char(1)
);

二つ目です。blog_table_bという名前で作成します。

create table <スキーマ名>.blog_table_b(
    ID integer identity(1,1) -- 自動採番で1始まりでid割り振り
    , some_data integer
    , table_name char(1)
);

最後に変換後のデータを入れるテーブルを作成します。テーブル名はblog_mart_tableとしました。

create table <スキーマ名>.blog_mart_table(
    ID integer identity(1,1) -- 自動採番で1始まりでid割り振り
    , some_data integer
    , table_name char(10)
    , created_at timestamp  not null
);

変換元のテーブルへデータを格納

まずはblog_table_aへデータを入れます。データを入れた後に、データの格納状況をチェックするためにSELECT文で念のため確認をしています。

insert into
    <スキーマ名>.blog_table_a
    (
        some_data
        , table_name
    )
values
    (111111, 'A')
    , (222111, 'A')
    , (222333, 'A')
;

select * from <スキーマ名>.blog_table_a;

SELECT文でチェックした結果は以下のようになりました。

id	some_data	table_name
1	111111	A
2	222111	A
3	222333	A

同様にしてblog_table_bへデータを入れます。

insert into
    <スキーマ名>.blog_table_b
    (
        some_data
        , table_name
    )
values
    (999111, 'B')
    , (222999, 'B')
    , (222333, 'B')
;

select * from <スキーマ名>.blog_table_b;

クエリ結果は以下のようになります。

id	some_data	table_name
1	999111	B
2	222999	B
3	222333	B

WITH句を使ってINSERT SELECTする

ここまでは準備のお話でした。ここからは、実際にWITH句を使ってINSERT文を実行してみます。

早速ですが、以下のようなINSERT文を書きました。

insert into
    <スキーマ名>.blog_mart_table
    (
        some_data
        , table_name
        , created_at
    )
with tmp_mart_table as(
    select
        *
    from
        <スキーマ名>.blog_table_a
    union
    select
        *
    from
        <スキーマ名>.blog_table_b
)
select
    some_data
    , case
        when table_name ='A' then 'fromTableA'
        when table_name ='B' then 'fromTableB'
    end
    as table_name
    , GETDATE() as created_at
from
    tmp_mart_table
;

SQLに慣れていない方からすると、少し長いかもしれません。 3つのブロックに分けて考えると分かりやすいです。

1つ目はINSERT文
- どのテーブルのどのカラムにデータを入れるか書いています。
2つ目はWITH句
- WITHで2つのテーブルをUNIONして、縦方向の結合をしています。同じカラム名のデータをまとめたかったためです。(ちなみに横方向の結合としてはJOINがあります。)
3つ目はWITH句の後のSELECT文
- CASE文を使ってどのテーブルから実行しているかを分かりやすくするために変換し、実行時のタイムスタンプをRedshiftの関数であるGETDATE関数を使って取得しています。

結果を以下のSELECT文で確認しました。

select
    *
from
    <スキーマ名>.blog_mart_table
order by id;

id	some_data	table_name	created_at
1	111111	fromTableA	2023-12-20 15:35:41.000
2	222111	fromTableA	2023-12-20 15:35:41.000
3	999111	fromTableB	2023-12-20 15:35:41.000
4	222333	fromTableA	2023-12-20 15:35:41.000
5	222999	fromTableB	2023-12-20 15:35:41.000
7	222333	fromTableB	2023-12-20 15:35:41.000